<?php

class PointProvider{
	/*
     * Get Available Point on Front End
     */
    
	public static function getPointOfUserFE($db, $conditions,$startRow){
        
        /******** Basic Query ********/	  
	   $query = "
            SELECT
                `t`.point, `t`.id,
                `t`.point,
                `t`.expire as `outlet`,
                `t`.transaction, `t`.redemption_id,
                `t`.des,
                `t`.created_id as `total`,
                `t`.created_date
            FROM #__wrd_point `t`
            WHERE `t`.user_id = ".intval($conditions['userId'])." AND `t`.point > 0
            ORDER BY `t`.id DESC
            LIMIT ".$startRow.",10
        ";
       
        /******** Query ********/
        $db->setQuery($query);
        $models = $db->loadObjectList();
        
        /****** Process Data ******/
        $totalPoint = 0;
        $transactionIds = '';
        $redemptionIds = '';
        
        
        if(!empty($models)){
            foreach($models as $item){
                $totalPoint += $item->point;
                if($item->des == 'import user' || $item->des == 'transaction') {
                    $transactionIds .= ($item->transaction > 0 ? $item->transaction : '').',';    
                }
                else {
                    $redemptionIds .= ($item->id).',';    
                }
                $item->outlet='';
                $item->total=''; 
            }
            $transactionIds = trim($transactionIds, ',');
            $redemptionIds = trim($redemptionIds, ',');
            
            
            if($transactionIds != '') {
                $query = "
                    SELECT
                        `t`.transaction_id     AS `transaction_id`,
                        `t`.id              AS `id`,
                        `o`.NAME            AS `outlet`,
                        `t`.total          AS `total`,
                        `t`.date_receipt   AS `date_receipt`
                        
                    FROM #__wrd_outlets `o`, #__wrd_transaction `t`
                    WHERE `t`.transaction_id IN (".$transactionIds.")
                          AND `t`.outlet = `o`.CODE
                    ORDER BY `t`.id DESC
                ";
                 $db->setQuery($query);
                 $transactions = $db->loadObjectList(); 
                 if(!empty($transactions)) {
                    if($transactions[0]->date_receipt != '')
                     {
                        $createdDate = explode(' ', $transactions[0]->date_receipt); 
                        $createdDate = explode('-',$transactions[0]->date_receipt);
                        $createdDate = mktime(0,0,0,$createdDate[1], $createdDate[2], $createdDate[0]);
                        $createdDate = date('M-Y', $createdDate);
                        $totalPoint = '<strong>'.$totalPoint.'</strong> points since <strong>'.$createdDate.'</strong>';
                     }
                     $dataTemp = array();
                     for($i=0; $i<count($transactions); $i++){
                        $dataTemp[$transactions[$i]->transaction_id] = $transactions[$i];
                     }
                     
                     for($i=0; $i<count($models); $i++){
                        $models[$i]->outlet = ($dataTemp[$models[$i]->transaction]->outlet != '' ? $dataTemp[$models[$i]->transaction]->outlet : $models[$i]->outlet);
                        $models[$i]->total = ($dataTemp[$models[$i]->transaction]->total > 0 ? number_format($dataTemp[$models[$i]->transaction]->total,0,'.',',') : '');
                     }
                 }
            }
            if($redemptionIds != ''){
                $query = "
                    SELECT
                        `t`.POINT    AS `point`,
                        `t`.point_id,
                        `p`.NAME     AS `name`
                    FROM #__wrd_redemption `t`
                        LEFT JOIN #__wrd_products `p`
                          ON `p`.id = `t`.product_id
                    WHERE `t`.point_id IN(".$redemptionIds.")
                    ORDER BY `t`.id DESC
                ";
                
                 $db->setQuery($query);
                 $remptions = $db->loadObjectList();
                 if(!empty($remptions)){
                     $dataTemp = array();
                     for($i=0;$i<count($remptions); $i++){
                         $dataTemp[$remptions[$i]->point_id] = array('point'=>$remptions[$i]->point, 'product'=>$remptions[$i]->name);
                     }
                     for($i=0; $i<count($models); $i++){
                        $models[$i]->point = ($dataTemp[$models[$i]->id]['point'] > 0 ? '-'.$dataTemp[$models[$i]->id]['point'] : $models[$i]->point);
                        $models[$i]->outlet = ($dataTemp[$models[$i]->id]['product'] != '' ? 'Product: '.$dataTemp[$models[$i]->id]['product'] : '');
                     }
                     
                 }
            }            
            else {
                if($models[0]->created_date != '')
                {
                    $createdDate = explode('-', $models[0]->created_date);
                    $createdDate = mktime(0,0,0,$createdDate[1], $createdDate[2], $createdDate[0]);
                    $createdDate = date('M-Y', $createdDate);
                    $totalPoint = '<strong>'.$totalPoint.'</strong> points since <strong>'.$createdDate.'</strong>';
                }
                
            }
        }
        
        
        /******** Return ********/
        return array(
            'totalPoint' => $totalPoint,
            'models' => $models,
            'transactions' => $transactions
        );
    }
   
    public static function selectBoxOutlet($name,$active=NULL,$db){        
        $states = array();
        $states = array_merge( $states, self::outletRaw($db));
        $state = JHTML::_('select.genericList', $states , $name, 'class="'.$class.'" size="1" '.$javascript, 'value', 'text', $active );
        return $state;
    }
    
    public static function outletRaw($db){        
        $query = 'SELECT code AS value, name AS text'
            .' FROM #__wrd_outlets WHERE published=1 AND cid = 01';
            
        $db->setQuery( $query );
        $data = $db->loadObjectList();
        return $data;
    }
     
    /*
     * Get Available Point
     */
    public static function getPointOfUser($db, $conditions){
        
        $query = "
            SELECT
                `p`.TRANSACTION AS `transaction`,
                `p`.id          AS `id`,
                `p`.outlet      AS `poutlet`,
                 SUM(`p`.point) AS `point`,
                `p`.user_id     AS `user_id`,
                `p`.des         AS `outlet`,
				`p`.des         AS `des`,
				`p`.created_date AS `created_date`,
                `p`.TYPE        AS `type`,
				`t`.date_receipt AS `date_receipt`
            FROM #__wrd_point AS p LEFT JOIN #__wrd_transaction AS t
				 ON `p`.transaction = `t`.transaction_id
			WHERE `p`.user_id = ".intval($conditions['userId'])." AND `p`.expired = 0
			GROUP BY transaction
			HAVING SUM(`p`.point)>0	
            ORDER BY `p`.id ASC
        ";
       
        /******** Query ********/
        $db->setQuery($query);
        $models = $db->loadObjectList();
        
        if(!empty($models)){
            $transactionIds = '';
            foreach($models as $item){
                $transactionIds .= ($item->transaction > 0 ? $item->transaction : '').',';
            }
            $transactionIds = trim($transactionIds, ',');
            
            if($transactionIds != ''){
                $query = "
                    SELECT
                        `o`.NAME         AS `outlet`,
                        `tr`.transaction_id
                    FROM #__wrd_outlets `o`,#__wrd_transaction `tr`
                    WHERE `tr`.transaction_id IN(".$transactionIds.")
                          AND `tr`.outlet = `o`.CODE
                    ORDER BY `tr`.id ASC
                ";
				
                $db->setQuery($query);
                $transaction = $db->loadObjectList();
                $transactionData = array();
                if(!empty($transaction)){
                    for($i=0; $i<count($transaction); $i++){
                        $transactionData[$transaction[$i]->transaction_id] = $transaction[$i]->outlet;
                    }
                    for($i=0; $i<count($models); $i++){
                        $models[$i]->outlet = ($transactionData[$models[$i]->transaction] != '' ? $transactionData[$models[$i]->transaction] : '');
                        
                    }
                }
            }
            else { 
                for($i=0; $i<count($models); $i++){
                    $models[$i]->outlet = '';
                }
            }
        }
        
        return $models;
    }
    
    /*
     * Get Available Point
     */
    public static function getCurrentPointOfUser($db, $conditions){
        
        $query = "
            SELECT
                SUM(`t`.point)       AS `total`
            FROM #__wrd_point `t`
            WHERE `t`.user_id = ".intval($conditions['userId'])."
            
        ";

        $db->setQuery($query);
        $models = $db->loadResult();
		
        return intval($models);
    }
	
    /*
     * Count current promotion
     */
    public static function countValidPromotion($db){
        
        /******** Basic Query ********/
        $query = "
            SELECT
                COUNT(*)
            FROM #__wrd_promotions `t`
            WHERE `t`.state = 1
                  AND ((YEAR(`t`.publish_up) = 0)
                       AND (YEAR(`t`.publish_down) = 0)
                        OR ('".date('Y-m-d')."' BETWEEN DATE(`t`.publish_up)
                            AND DATE(`t`.publish_down)))
        ";
        
        /******** Query ********/
        $db->setQuery($query); 
        $model = $db->loadResult();
        
        /******** Return ********/
        return $model;
    }
    
    /*
     * Count current redemption
     */
    public static function countValidRedemption($db){
        
        /******** Basic Query ********/
        $query = "
            SELECT
                COUNT(*) AS `total`
            FROM #__wrd_products `t`
            WHERE `t`.published = 1
                  AND `t`.quantity > 0
                  AND ((YEAR(`t`.published_up) = 0)
                       AND (YEAR(`t`.published_down) = 0)
                        OR ('".date('Y-m-d')."' BETWEEN DATE(`t`.published_up)
                            AND DATE(`t`.published_down)))
        ";
        
        /******** Query ********/
        $db->setQuery($query); 
        $model = $db->loadResult();
        
        /******** Return ********/
        return $model;
    }
    
    /*
     * Get Product categories
     */
    public static function productCategories($db){
        
        /******** Basic Query ********/
        $query = "
            SELECT
                `t`.id,
                `t`.title
            FROM #__wrd_pcategory `t`
            WHERE `t`.published = 1
            ORDER BY `t`.id ASC             
        ";
        
        /******** Query ********/
        $db->setQuery($query); 
        $model = $db->loadObjectList();
        
        /******** Return ********/
        return $model;
    }
    
    /*
     * Get Latest promotions
     */
    public static function latestPromotion($db, $lang, $start=0, $limit=0){
        
        /******** Basic Query ********/
        $query = "
            SELECT
                `t`.*
            FROM #__wrd_promotions `t`
            WHERE `t`.state = 1
                  AND ((YEAR(`t`.publish_up) = 0)
                       AND (YEAR(`t`.publish_down) = 0)
                        OR ('".date('Y-m-d')."' BETWEEN DATE(`t`.publish_up)
                            AND DATE(`t`.publish_down)))
                  AND (`t`.LANGUAGE = '*'
                        OR `t`.LANGUAGE = '".$lang."')
            ORDER BY `t`.id DESC            
        ";
        if($limit>0) {
            $query .= " LIMIT ".$start.",".$limit." ";
        }
        
        /******** Query ********/
        $db->setQuery($query); 
        $model = $db->loadObjectList();
        
        /******** Return ********/
        return $model;
    }
    
    /*
     * Get Latest promotions
     */
    public static function latestRedemptions($db, $start=0, $limit=0){
        
        /******** Basic Query ********/
        $query = "
            SELECT
                `f`.NAME       AS `image`,
                `t`.*
            FROM #__wrd_products `t`
                LEFT JOIN #__wrd_file `f`
                  ON `f`.TYPE = 'image'
                    AND `t`.id = `f`.product_id
            WHERE `t`.published = 1
                  AND `t`.quantity > 0
                  AND ((YEAR(`t`.published_up) = 0)
                       AND (YEAR(`t`.published_down) = 0)
                        OR ('".date('Y-m-d')."' BETWEEN DATE(`t`.published_up)
                            AND DATE(`t`.published_down)))
            GROUP BY `t`.cat_id
            ORDER BY `t`.id DESC
        ";
        if($limit>0) {
            $query .= " LIMIT ".$start.",".$limit." ";
        }
        
        /******** Query ********/
        $db->setQuery($query); 
        $model = $db->loadObjectList();
        
        /******** Return ********/
        return $model;
    }
    
    /*
     * Query product with id
     */
    public static function getProduct($db, $conditions){
        
        /******** Basic Query ********/
        $query = "
            SELECT
                `t`.*,
                `f`.NAME AS `image`
            FROM #__wrd_products `t`
                LEFT JOIN #__wrd_file `f`
                  ON `t`.id = `f`.product_id
                    AND `f`.TYPE = 'image'
            WHERE `t`.id = ".intval($conditions['pid'])."
            GROUP BY `t`.id
        ";
        
        /******** Query ********/
        $db->setQuery($query);
        $model = $db->loadObject();
        
        /******** Return ********/
        return $model;
    }
    
    /*
     * Get Point
     */
    public static function getPointById($db, $conditions){
        
        /******** Basic Query ********/
        $sql = "
            SELECT
                `t`.id,
                `t`.point
            FROM #__wrd_point `t`
            WHERE `t`.id = ".intval($conditions['id'])."
        ";
        
        /******** Query ********/
        $db->setQuery($sql);
        $model = $db->loadObject();
        
        /******** Return ********/
        return $model;
    }
    
    /*
     * Get Redemption
     */
    public static function getRedemptionByCode($db, $conditions){
        
        /******** Basic Query ********/
        $sql = "
            SELECT
                `t`.*,`u`.username AS created_by,`uu`.username AS updated_by,
                `p`.NAME AS `product`,`p`.voucher,`p`.p_code,`p`.price, 
				 `o`.NAME AS `outlet`,`outlet`.name as requested_outlet_name
            FROM #__wrd_outlets `o`, #__wrd_redemption `t`
            LEFT JOIN #__wrd_products `p` ON `p`.id = `t`.product_id
			LEFT JOIN #__wrd_outlets `outlet` ON `outlet`.code = `t`.requested_outlet
			LEFT JOIN #__users `u` ON `t`.created_id = `u`.id
			LEFT JOIN #__users `uu` ON `t`.updated_by = `uu`.id
			WHERE `t`.CODE = '".$conditions['code']."' AND `o`.cid = '01'
                              AND `t`.pick_up_at = `o`.CODE
        ";
        //echo $sql; 
        /******** Query ********/
        $db->setQuery($sql);
        $model = $db->loadObjectList();
        /******** Return ********/
        return $model;
    }

    public static function getTransactionByCode($db, $conditions){
		
		$sql = "SELECT `pt`.redemption_id,`pt`.outlet as poulet,`pt`.type,`pt`.transaction,`pt`.des,`pt`.point,`tr`.outlet,`o`.name 
				FROM #__wrd_point AS `pt` 
				LEFT JOIN  #__wrd_transaction as `tr` ON `pt`.transaction = `tr`.transaction_id 
				LEFT JOIN #__wrd_outlets AS `o` ON `o`.code = `pt`.outlet 
				WHERE `pt`.redemption_id ='".$conditions['code']."'";
       
		$db->setQuery($sql);
        $model = $db->loadObjectList();
        return $model;
    }
	
    /*
     * Get User
     */
    public static function getUserById($db, $conditions){
        $sql = "
            SELECT
            	`t`.address,
            	`t`.phone,
            	`t`.member_id,
                `t`.f_name,
                `t`.l_name,
                `t`.id
            FROM #__wrd_profile `t`
            WHERE `t`.user_id = ".intval($conditions['id']).";
        ";
      	//echo $sql;
        $db->setQuery($sql);
        $model = $db->loadObject();
        
      
        return $model;
    }
    
    /*
     * Check quantity of product
     */
    public static function checkProductQuantity($db, $conditions){
        
        /******** Basic Query ********/
        $sql = "
            SELECT
                * 
            FROM #__wrd_products
            WHERE id IN(".$conditions['ids'].")
        ";
        
        $db->setQuery($sql);
        $models = $db->loadObjectList();
        
        return $models;
    }
    
    /*
     * Show Profile Field
     */
    public static function showProfileField($db)
    {
        $sql = "
            SHOW COLUMNS FROM #__wrd_profile 
        ";
        
        $db->setQuery($sql);
        $models = $db->loadObjectList();
        $data = array();
        if(!empty($models)) {
            foreach($models as $item) {
                $data[$item->Field] = array('value'=>$item->Field, 'text'=>ucfirst($item->Field));
            }
        }
        
        return $data;
    }

    /*
     * Show Profile Field
     */
    public static function getConfigByName($db, $seoName)
    {
        $sql = "
            SELECT
                `t`.VALUE
            FROM #__wrd_configs `t`
            WHERE `t`.state = 1
                  AND `t`.seo_name = '".strval($seoName)."'
        ";
        
        $db->setQuery($sql);
        $value = $db->loadResult();
        
        return $value;
    }
}
?>